﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BillMgr.Utils
{
    public class MYSQLHelperTrans
    {
        private MySqlConnection connection;
        private MySqlTransaction trans;

        public MYSQLHelperTrans()
        {
            this.connection = ConnectionCreater.GetConnection();
            connection.Open();
            trans = connection.BeginTransaction();
        }
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public void ExecuteSqlTran(Dictionary<String, MySqlParameter[]> SQLList)
        {

            MySqlCommand cmd = new MySqlCommand();

            //循环
            foreach (KeyValuePair<String, MySqlParameter[]> myDE in SQLList)
            {
                string cmdText = myDE.Key;
                PrepareCommand(cmd, connection, trans, cmdText, myDE.Value);
                int val = cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的MySqlParameter[]）</param>
        public void ExecuteSqlTran(Dictionary<MySqlParameter[], String> SQLList)
        {

            MySqlCommand cmd = new MySqlCommand();

            //循环
            foreach (KeyValuePair<MySqlParameter[], String> myDE in SQLList)
            {
                string cmdText = myDE.Value;
                PrepareCommand(cmd, connection, trans, cmdText, myDE.Key);
                int val = cmd.ExecuteNonQuery();
            }
        }
        public void ExecuteSqlTran(List<Object[]> sqls)
        {


            MySqlCommand cmd = new MySqlCommand();


            //循环
            foreach (Object[] myDE in sqls)
            {
                string cmdText = myDE[0].ToString();
                PrepareCommand(cmd, connection, trans, cmdText, (MySqlParameter[])myDE[1]);
                int val = cmd.ExecuteNonQuery();
            }

        }

        public void ExecuteSqlTran(String strSql, MySqlParameter[] cmdParameter)
        {

            MySqlCommand cmd = new MySqlCommand();
            //循环
            PrepareCommand(cmd, connection, trans, strSql, cmdParameter);
            int val = cmd.ExecuteNonQuery();

        }
        public void ExecuteSqlTran(String strSql)
        {

            MySqlCommand cmd = new MySqlCommand(strSql, connection);
            //循环

            int val = cmd.ExecuteNonQuery();

        }
        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="SQLString">查询语句</param>
        /// <returns>DataSet</returns>
        public DataTable Query(String SQLString, MySqlParameter[] cmdParms)
        {

            MySqlCommand cmd = new MySqlCommand();
            PrepareCommand(cmd, connection, trans, SQLString, cmdParms);
            using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                DataTable dt = null;
                try
                {
                    da.Fill(ds);
                    if (ds == null)
                        dt = null;
                    else
                        dt = ds.Tables[0];
                    cmd.Parameters.Clear();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return dt;
            }
        }
        public void RollBack()
        {

            trans.Rollback();
            connection.Close();
        }

        public void Commit()
        {
            trans.Commit();
            connection.Close();

        }

        /// <summary>
        /// 组合参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="trans"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText, MySqlParameter[] cmdParms)
        {

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
                cmd.Transaction = trans;
            cmd.CommandType = CommandType.Text;//cmdType;
            if (cmdParms != null)
            {
                foreach (MySqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }
        public int GetMaxID(String fieldName, String strtabname)
        {
            string strsql = "select max(" + fieldName + ") from " + strtabname;
            object obj = GetSingle(strsql, connection);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return int.Parse(obj.ToString());
            }

        }
        private static object GetSingle(string SQLString, MySqlConnection connection)
        {
            MySqlCommand cmd = new MySqlCommand(SQLString, connection);
            try
            {
                object obj = cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
